﻿using System;
using System.Collections;
using System.Collections.Generic;
using System.Data;
using System.Data.OracleClient;
using System.Text;
using ReflectionStudio.Core.Events;
namespace WPFExcelReport
{
    //
    public class AB05Data : BaseFunction
    {

        //
        public AB05Data()
            : base()
        {
            //1041984944
        }
        public AB05Data(Dictionary<string, object> DicGlobalPara)
            : base(DicGlobalPara)
        {
        }

        public DataSet GetWBBST(DataSet dsTarget, DataRow dwBATCH)
        {
            string TableName = "WBBST";
            try
            {

                orlReadCommand = OracleDB.CreateCommand();
                orlReadCommand.CommandText = "SELECT WBBSTKNR,WBBART,WBBFARBE,WBBVAR,WBBSSTKLG, "
                       + " WBBISTKLG,WBBZENSUR||WBBZENSURKZ AS QUANLITY,WBBLO,WBBLB,WBBLP,WBBTP, "
                       + " WBBDPART,WBBEINDAT,WBBBEM  FROM WBBST WHERE WBBMANDANT = '00' "
                       + " AND WBBAUFKRS = :WBBAUFKRS AND WBBAUFLFD=:WBBAUFLFD AND WBBAUFPOS=:WBBAUFPOS AND WBBTP = :WBBTP ";
                orlReadCommand.Parameters.Add("WBBAUFKRS", OracleType.NVarChar).Value = dwBATCH["WBBAUFKRS"].ToString();
                orlReadCommand.Parameters.Add("WBBAUFLFD", OracleType.NVarChar).Value = dwBATCH["WBBAUFLFD"].ToString();
                orlReadCommand.Parameters.Add("WBBAUFPOS", OracleType.NVarChar).Value = dwBATCH["WBBAUFPOS"].ToString();
                orlReadCommand.Parameters.Add("WBBTP", OracleType.NVarChar).Value = dwBATCH["WBBTP"].ToString();
                dsResult = Connect2Proxy(orlReadCommand);
                if (dsResult.Tables.Count > 0)
                {
                    dsResult.Tables[0].TableName = TableName;
                    Queue Q100 = new Queue();
                    Q100.Enqueue("WBBSSTKLG");
                    Q100.Enqueue("WBBISTKLG");
                    DivideBy1000(dsResult.Tables[TableName], Q100);
                    dsResult.AcceptChanges();

                    byte[] b;
                    string value;
                    foreach (DataRow dw in dsResult.Tables[TableName].Rows)
                    {
                        foreach (DataColumn dc in dsResult.Tables[TableName].Columns)
                        {
                            if (dc.ColumnName == "WBBBEM")
                            {
                                b = Encoding.GetEncoding("ISO-8859-1").GetBytes(dw[dc.ColumnName].ToString().Trim());
                                value = Encoding.GetEncoding("gb2312").GetString(b);
                                dw[dc.ColumnName] = value;
                            }

                        }
                    }
                    Tracer.Speak("共有：" + dsResult.Tables[TableName].Rows.Count + "坯布布卷。");
                }
                foreach (DataTable dtTemp in dsResult.Tables)
                {
                    if (dsTarget.Tables.Contains(TableName))
                    {
                        dsTarget.Tables[TableName].Clear();
                    }
                    dsTarget = ImportDataTable(dsTarget, dtTemp);
                }
                dsTarget.AcceptChanges();
            }
            catch (Exception ex)
            {
                Tracer.Error(ex.StackTrace, ex); ;
            }
            return dsTarget;
        }
        public DataSet GetLOT(DataSet dsTarget, DataRow dwBATCH)
        {
            string TableName = "LOT";
            try
            {

                orlReadCommand = OracleDB.CreateCommand();
                orlReadCommand.CommandText = "SELECT distinct WBBAUFKRS,WBBAUFLFD,WBBAUFPOS,WBBTP,WBBDPART, "
                       + " WBBAUFKRS||'.'||TO_NUMBER(WBBAUFLFD)||'.'||TO_NUMBER(WBBAUFPOS)||'.'||TO_NUMBER(WBBTP) AS ORDERNO "
                       + " FROM WBBST WHERE WBBMANDANT = '00' "
                       + " AND WBBAUFKRS = :WBBAUFKRS AND WBBAUFLFD=:WBBAUFLFD AND WBBAUFPOS=:WBBAUFPOS AND WBBTP = :WBBTP ";
                orlReadCommand.Parameters.Add("WBBAUFKRS", OracleType.NVarChar).Value = dwBATCH["WBBAUFKRS"].ToString();
                orlReadCommand.Parameters.Add("WBBAUFLFD", OracleType.NVarChar).Value = dwBATCH["WBBAUFLFD"].ToString();
                orlReadCommand.Parameters.Add("WBBAUFPOS", OracleType.NVarChar).Value = dwBATCH["WBBAUFPOS"].ToString();
                orlReadCommand.Parameters.Add("WBBTP", OracleType.NVarChar).Value = dwBATCH["WBBTP"].ToString();
                dsResult = Connect2Proxy(orlReadCommand);
                if (dsResult.Tables.Count > 0)
                {
                    dsResult.Tables[0].TableName = TableName;
                    Tracer.Speak("分" + dsResult.Tables[TableName].Rows + "个批次");
                }

                foreach (DataTable dtTemp in dsResult.Tables)
                {
                    if (dsTarget.Tables.Contains(TableName))
                    {
                        dsTarget.Tables[TableName].Clear();
                    }
                    dsTarget = ImportDataTable(dsTarget, dtTemp);
                }
                dsTarget.AcceptChanges();
            }
            catch (Exception ex)
            {
                Tracer.Error(ex.StackTrace, ex);
            }
            return dsTarget;
        }
        public DataSet GetBATCH(DataSet dsTarget, DataRow dwABPOS)
        {
            string TableName = "BATCH";
            try
            {

                orlReadCommand = OracleDB.CreateCommand();
                orlReadCommand.CommandText = "SELECT DISTINCT WBBAUFKRS,WBBAUFLFD, WBBAUFPOS,WBBTP "
                       + " FROM WBBST WHERE WBBMANDANT = '00' "
                       + " AND WBBAUFKRS = :WBBAUFKRS AND WBBAUFLFD=:WBBAUFLFD AND WBBAUFPOS=:WBBAUFPOS";
                orlReadCommand.Parameters.Add("WBBAUFKRS", OracleType.NVarChar).Value = dwABPOS["ABPAUFKRS"].ToString();
                orlReadCommand.Parameters.Add("WBBAUFLFD", OracleType.NVarChar).Value = dwABPOS["ABPAUFLFD"].ToString();
                orlReadCommand.Parameters.Add("WBBAUFPOS", OracleType.NVarChar).Value = dwABPOS["ABPAUFPOS"].ToString();
                dsResult = Connect2Proxy(orlReadCommand);
                if (dsResult.Tables.Count > 0)
                {
                    dsResult.Tables[0].TableName = TableName;
                    string WBBTP = "";
                    foreach (DataRow dwTemp in dsResult.Tables[0].Rows)
                    {
                        WBBTP += dwTemp["WBBTP"].ToString();
                    }
                    Tracer.Speak("分批是：" + WBBTP);
                }
                foreach (DataTable dtTemp in dsResult.Tables)
                {
                    if (dsTarget.Tables.Contains(TableName))
                    {
                        dsTarget.Tables[TableName].Clear();
                    }
                    dsTarget = ImportDataTable(dsTarget, dtTemp);
                }
                dsTarget.AcceptChanges();
            }
            catch (Exception ex)
            {
                Tracer.Error(ex.StackTrace, ex);
            }
            return dsTarget;
        }
        public DataSet GetABPOS(DataSet dsTarget, DataRow dwBATCH)
        {
            string TableName = "ABPOS";
            try
            {
                orlReadCommand = OracleDB.CreateCommand();
                orlReadCommand.CommandText = "SELECT Distinct LSNAME1,ABPAUFKRS,ABPAUFLFD,ABPAUFPOS,ABPART,ABPVAR, "
                    + " ABPFARBE,'' AS COLORDES, "
                    + " ABPSTARTDAT,ABPENDDAT, '' AS REMARK,ABPWAKZ,'' AS COMPOSITION,'' AS WARWBRT ,'' AS WAFBRTL, "
                    + " ROUND(ABPSOLLMENGE2/100/0.9144,1) AS YARDQTY,ROUND(ABPSOLLMENGE1/100,1) AS GREIGEQTY,ABPBESCHICH, "
                    + " TRUNC(ABPSOLLMENGE8/100) AS QTY,'' AS WALAEAEND,'' AS COMS,'' AS PRICE  "
                    + " FROM INTEX.ABPOS,INTEX.LIEFDAT,SDDAT "
                    + " WHERE  ABPMANDANT = LSMANDANT  AND ABPLIEFNR = LSLIEFNR  AND ABPMANDANT = '00' "
                    + " AND ABPAUFKRS = :ABPAUFKRS AND ABPAUFLFD=:ABPAUFLFD AND ABPAUFPOS = :ABPAUFPOS "
                     ;
                orlReadCommand.Parameters.Add("ABPAUFKRS", OracleType.NVarChar).Value = dwBATCH["WBBAUFKRS"].ToString();
                orlReadCommand.Parameters.Add("ABPAUFLFD", OracleType.NVarChar).Value = dwBATCH["WBBAUFLFD"].ToString();
                orlReadCommand.Parameters.Add("ABPAUFPOS", OracleType.NVarChar).Value = dwBATCH["WBBAUFPOS"].ToString();
                dsResult = Connect2Proxy(orlReadCommand);
                if (dsResult.Tables.Count > 0)
                {
                    dsResult.Tables[0].TableName = TableName;

                    #region comments
                    foreach (DataRow rowABPOS in dsResult.Tables["ABPOS"].Rows)
                    {
                        DataSet setUI = new DataSet();

                        try
                        {

                            string COMMENTS = "";
                            orlReadCommand = OracleDB.CreateCommand();
                            orlReadCommand.CommandText = "SELECT trim(TXTKTEXT) AS COMS "
                                + " FROM TXTIND A,TXTKON B WHERE A.TXTIMANDANT = B.TXTKMANDANT "
                                + " AND A.TXTIPIND = B.TXTKPIND AND B.TXTKPOS <50 AND A.TXTITXTART = 'WAE' "
                                + " AND SUBSTR(A.TXTISIND,15,20) = :ArticleNum "
                                + " AND SUBSTR(A.TXTISIND,35,2)= :ArticleVar AND SUBSTR(A.TXTISIND,37,1)='C' "
                                + " AND SUBSTR(A.TXTISIND,38,10) = :ArticleColor ";
                            orlReadCommand.Parameters.Add("ArticleNum", OracleType.NVarChar).Value = rowABPOS["ABPART"].ToString();
                            orlReadCommand.Parameters.Add("ArticleVar", OracleType.NVarChar).Value = rowABPOS["ABPVAR"].ToString().Trim();
                            orlReadCommand.Parameters.Add("ArticleColor", OracleType.NVarChar).Value = rowABPOS["ABPFARBE"].ToString();
                            setUI = Connect2Proxy(orlReadCommand);

                            if (setUI.Tables.Count > 0)
                            {
                                setUI.Tables[0].TableName = "WAFARBENCOMS";
                                foreach (DataRow rowTemp1 in setUI.Tables["WAFARBENCOMS"].Rows)
                                {

                                    COMMENTS += rowTemp1["COMS"].ToString() + Environment.NewLine;
                                }
                            }
                            if (COMMENTS != "")
                            {
                                rowABPOS["COMS"] = COMMENTS;
                                Tracer.Speak("注意：" + COMMENTS);
                            }
                        }
                        catch (Exception ex)
                        {
                            Tracer.Error(ex.StackTrace, ex);
                        }
                    }
                    #endregion
                    byte[] b;
                    string value;
                    foreach (DataRow dw in dsResult.Tables[TableName].Rows)
                    {
                        foreach (DataColumn dc in dsResult.Tables[TableName].Columns)
                        {

                            b = Encoding.GetEncoding("ISO-8859-1").GetBytes(dw[dc.ColumnName].ToString().Trim());
                            value = Encoding.GetEncoding("gb2312").GetString(b);
                            dw[dc.ColumnName] = value;
                        }
                    }
                }
                foreach (DataTable dtTemp in dsResult.Tables)
                {
                    DataSet setUI = new DataSet();
                    setUI = ImportDataTable(setUI, dtTemp);
                    DataTable tableABPOS = setUI.Tables["ABPOS"];
                    foreach (DataRow dwTmp in tableABPOS.Rows)
                    {
                        string order = dwTmp["ABPAUFKRS"].ToString() + dwTmp["ABPAUFLFD"].ToString() + dwTmp["ABPAUFPOS"].ToString();
                        dwTmp["REMARK"] = GetCommentsFromTXTIND("ABP", order);
                        setUI = GetWASTAMM(setUI, dwTmp);
                        setUI = GetABFOLGE(setUI, dwTmp);
                        setUI = GetABPR(setUI, dwTmp);
                        setUI = GetCOLORDES(setUI, dwTmp);
                    }

                    if (dsTarget.Tables.Contains(TableName))
                    {
                        dsTarget.Tables[TableName].Clear();
                    }
                    dsTarget = ImportDataTable(dsTarget, setUI.Tables["ABPOS"]);
                }
                dsTarget.AcceptChanges();
            }
            catch (Exception ex)
            {
                Tracer.Error(ex.StackTrace, ex);
            }
            return dsTarget;
        }
        public DataSet GetABKOPF(DataSet dsTarget, DataRow dwBATCH)
        {
            string TableName = "ABKOPF";
            if (dwBATCH == null)
                return null;
            try
            {
                orlReadCommand = OracleDB.CreateCommand();
                orlReadCommand.CommandText = "SELECT A.ABKKDAUFTNR,A.ABKKDZEICH ,B.HRNNAME,A.ABKAUFTTXT1,A.ABKAEDAT "
                    + " FROM ABKOPF A,HRSTAMM B WHERE A.ABKMANDANT = B.HRMANDANT AND A.ABKARTSB = B.HRBENID "
                    + " AND A.ABKAUFKRS = :ABKAUFKRS AND A.ABKAUFLFD=:ABKAUFLFD  "
                     ;
                orlReadCommand.Parameters.Add("ABKAUFKRS", OracleType.NVarChar).Value = dwBATCH["WBBAUFKRS"].ToString();
                orlReadCommand.Parameters.Add("ABKAUFLFD", OracleType.NVarChar).Value = dwBATCH["WBBAUFLFD"].ToString();
                dsResult = Connect2Proxy(orlReadCommand);
                if (dsResult.Tables.Count > 0)
                {
                    dsResult.Tables[0].TableName = TableName;

                    byte[] b;
                    string value;
                    foreach (DataRow dw in dsResult.Tables[TableName].Rows)
                    {
                        foreach (DataColumn dc in dsResult.Tables[TableName].Columns)
                        {
                            b = Encoding.GetEncoding("ISO-8859-1").GetBytes(dw[dc.ColumnName].ToString().Trim());
                            value = Encoding.GetEncoding("gb2312").GetString(b);
                            dw[dc.ColumnName] = value;

                        }
                    }
                }
                foreach (DataTable dtTemp in dsResult.Tables)
                {
                    if (dsTarget.Tables.Contains(TableName))
                    {
                        dsTarget.Tables[TableName].Clear();
                    }
                    dsTarget = ImportDataTable(dsTarget, dtTemp);
                }
                dsTarget.AcceptChanges();
            }
            catch (Exception ex)
            {
                Tracer.Error(ex.StackTrace, ex);
            }
            return dsTarget;
        }
        public DataSet GetWASTAMM(DataSet dsTarget, DataRow dwABPOS)
        {
            string TableName = "WASTAMM";
            try
            {
                orlReadCommand = OracleDB.CreateCommand();
                orlReadCommand.CommandText = "SELECT distinct A.WAART,A.WAVAR,A.WARWBRT/10 AS WARWBRT,A.WAFBRTL/10 AS WAFBRTL, "
                    + " A.WALAEAEND/100 AS WALAEAEND,B.COMPOSITION "
                    + " FROM WASTAMM A,FABRIC_COMPOSITION B "
                    + " WHERE A.WAMANDANT = B.STUELIMANDANT AND A.WAART = B.WAART AND A.WAVAR = B.WAVAR "
                    + " AND trim(A.WAART) = :WAART AND TRIM(A.WAVAR)=:WAVAR AND trim(B.TYPE) = :TYPE "
                     ;
                orlReadCommand.Parameters.Add("WAART", OracleType.NVarChar).Value = dwABPOS["ABPART"].ToString().Trim();
                orlReadCommand.Parameters.Add("WAVAR", OracleType.NVarChar).Value = dwABPOS["ABPVAR"].ToString().Trim();
                orlReadCommand.Parameters.Add("TYPE", OracleType.NVarChar).Value = dwABPOS["ABPWAKZ"].ToString().Trim();
                dsResult = Connect2Proxy(orlReadCommand);
                if (dsResult.Tables.Count > 0)
                {
                    dsResult.Tables[0].TableName = TableName;

                    byte[] b;
                    string value;
                    foreach (DataRow dw in dsResult.Tables[TableName].Rows)
                    {
                        foreach (DataColumn dc in dsResult.Tables[TableName].Columns)
                        {
                            if (dc.ColumnName == "COMPOSITION")
                            {
                                b = Encoding.GetEncoding("ISO-8859-1").GetBytes(dw[dc.ColumnName].ToString().Trim());
                                value = Encoding.GetEncoding("gb2312").GetString(b);
                                dw[dc.ColumnName] = value;
                            }
                        }
                        dwABPOS["WARWBRT"] = dw["WARWBRT"];
                        dwABPOS["WAFBRTL"] = dw["WAFBRTL"];
                        dwABPOS["WALAEAEND"] = dw["WALAEAEND"];
                        dwABPOS["COMPOSITION"] = dw["COMPOSITION"];
                    }
                }
                foreach (DataTable dtTemp in dsResult.Tables)
                {
                    if (dsTarget.Tables.Contains(TableName))
                    {
                        dsTarget.Tables[TableName].Clear();
                    }
                    dsTarget = ImportDataTable(dsTarget, dtTemp);
                }
                dsTarget.AcceptChanges();
            }
            catch (Exception ex)
            {
                Tracer.Error(ex.StackTrace, ex);
            }
            return dsTarget;
        }
        public DataSet GetABFOLGE(DataSet dsTarget, DataRow dwABPOS)
        {
            string TableName = "ABFOLGE";
            try
            {
                orlReadCommand = OracleDB.CreateCommand();
                orlReadCommand.CommandText = "SELECT DISTINCT B.LSNAME1 FROM ABFOLGE A,LIEFDAT B "
                    + " WHERE A.ABFMANDANT = B.LSMANDANT AND A.ABFLIEFNR = B.LSLIEFNR "
                    + " AND ABFMANUELKZ = 'N' AND ABFMANDANT = '00' AND ABFAUFKRS = :ABFAUFKRS  "
                    + " AND ABFAUFLFD = :ABFAUFLFD  AND ABFAUFPOS = :ABFAUFPOS  "
                     ;
                orlReadCommand.Parameters.Add("ABFAUFKRS", OracleType.NVarChar).Value = dwABPOS["ABPAUFKRS"].ToString().Trim();
                orlReadCommand.Parameters.Add("ABFAUFLFD", OracleType.NVarChar).Value = dwABPOS["ABPAUFLFD"].ToString().Trim();
                orlReadCommand.Parameters.Add("ABFAUFPOS", OracleType.NVarChar).Value = dwABPOS["ABPAUFPOS"].ToString().Trim();
                dsResult = Connect2Proxy(orlReadCommand);
                if (dsResult.Tables.Count > 0)
                {
                    dsResult.Tables[0].TableName = TableName;

                    byte[] b;
                    string value;
                    string ColumnName = "";
                    foreach (DataRow dw in dsResult.Tables["ABFOLGE"].Rows)
                    {
                        ColumnName = "LSNAME1";
                        b = Encoding.GetEncoding("ISO-8859-1").GetBytes(dw[ColumnName].ToString().Trim());
                        value = Encoding.GetEncoding("gb2312").GetString(b);
                        dw[ColumnName] = value;

                        dwABPOS["LSNAME1"] = dw["LSNAME1"];
                    }
                }
                foreach (DataTable dtTemp in dsResult.Tables)
                {
                    if (dsTarget.Tables.Contains(TableName))
                    {
                        dsTarget.Tables[TableName].Clear();
                    }
                    dsTarget = ImportDataTable(dsTarget, dtTemp);
                }
                dsTarget.AcceptChanges();
            }
            catch (Exception ex)
            {
                Tracer.Error(ex.StackTrace, ex);
            }
            return dsTarget;
        }
        public DataSet GetCOLORDES(DataSet dsTarget, DataRow dwABPOS)
        {
            string TableName = "COLORDES";
            try
            {
                orlReadCommand = OracleDB.CreateCommand();
                orlReadCommand.CommandText = "SELECT DISTINCT SUBSTR(SDDATEN,2,30) AS COLORDES FROM SDDAT "
                    + " WHERE SUBSTR(SDKEY,1,2)='00' AND trim(SUBSTR(SDKEY,3,10))=:ABPBESCHICH  AND SDSA = '9998' "
                     ;
                orlReadCommand.Parameters.Add("ABPBESCHICH", OracleType.NVarChar).Value = dwABPOS["ABPBESCHICH"].ToString().Trim();
                dsResult = Connect2Proxy(orlReadCommand);
                if (dsResult.Tables.Count > 0)
                {
                    dsResult.Tables[0].TableName = TableName;

                    byte[] b;
                    string value;
                    string ColumnName = "";
                    foreach (DataRow dw in dsResult.Tables["COLORDES"].Rows)
                    {
                        ColumnName = "COLORDES";
                        b = Encoding.GetEncoding("ISO-8859-1").GetBytes(dw[ColumnName].ToString().Trim());
                        value = Encoding.GetEncoding("gb2312").GetString(b);
                        dw[ColumnName] = value;

                        dwABPOS["COLORDES"] = dw["COLORDES"];
                    }
                }
                foreach (DataTable dtTemp in dsResult.Tables)
                {
                    if (dsTarget.Tables.Contains(TableName))
                    {
                        dsTarget.Tables[TableName].Clear();
                    }
                    dsTarget = ImportDataTable(dsTarget, dtTemp);
                }
                dsTarget.AcceptChanges();
            }
            catch (Exception ex)
            {
                Tracer.Error(ex.StackTrace, ex);
            }
            return dsTarget;
        }
        public DataSet GetABPR(DataSet dsTarget, DataRow dwABPOS)
        {
            string TableName = "ABPR";
            try
            {
                orlReadCommand = OracleDB.CreateCommand();
                orlReadCommand.CommandText = "SELECT DISTINCT ARWERT/1000 AS PRICE FROM ABPR  "
                + " WHERE ARAUFKRS =:ARAUFKRS  AND ARAUFLFD = :ARAUFLFD AND ARAUFPOS = :ARAUFPOS AND ARAFOLGE = '000' "
                     ;
                orlReadCommand.Parameters.Add("ARAUFKRS", OracleType.NVarChar).Value = dwABPOS["ABPAUFKRS"].ToString();
                orlReadCommand.Parameters.Add("ARAUFLFD", OracleType.NVarChar).Value = dwABPOS["ABPAUFLFD"].ToString();
                orlReadCommand.Parameters.Add("ARAUFPOS", OracleType.NVarChar).Value = dwABPOS["ABPAUFPOS"].ToString();
                dsResult = Connect2Proxy(orlReadCommand);
                if (dsResult.Tables.Count > 0)
                {
                    dsResult.Tables[0].TableName = TableName;
                }
                foreach (DataRow dw in dsResult.Tables[TableName].Rows)
                {
                    dwABPOS["PRICE"] = dw["PRICE"];
                    Tracer.Speak("价格：" + dw["PRICE"].ToString() + "元");
                }
                foreach (DataTable dtTemp in dsResult.Tables)
                {
                    if (dsTarget.Tables.Contains(TableName))
                    {
                        dsTarget.Tables[TableName].Clear();
                    }
                    dsTarget = ImportDataTable(dsTarget, dtTemp);
                }
                dsTarget.AcceptChanges();
            }
            catch (Exception ex)
            {
                Tracer.Error(ex.StackTrace, ex);
            }
            return dsTarget;
        }
        public DataSet BuildData(DataSet dsTarget, DataRow dwBATCH)
        {
            try
            {
                dsTarget = GetABKOPF(dsTarget, dwBATCH);
                dsTarget = GetABPOS(dsTarget, dwBATCH);
                dsTarget = GetLOT(dsTarget, dwBATCH);
            }
            catch (Exception ex)
            {
                Tracer.Error(ex.StackTrace, ex);
            }
            return dsTarget;
        }

    }
}